package cn.fanxin.tools;

import cn.fanxin.utils.MyJdbcUtils;
import cn.fanxin.utils.ShellUtils;
import com.mysql.fabric.xmlrpc.base.Array;
import com.mysql.jdbc.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import static java.util.Calendar.DECEMBER;
import static java.util.Calendar.JANUARY;

/**
 * @Author: china-fanxin
 * @Website: https://fan-xin.cn
 * @Description: 获取配置信息的类
 * @Date: 2022/11/20 18:31
 * @Param:
 * @return:
 */
public class CommonFun {

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 读取指标配置表，返回配信信息集合
     * @Date: 2022/11/20 15:52
     * @Param: []
     * @return: java.util.HashMap<java.lang.String, java.lang.String>
     */
    public static HashMap<String, String> getKpiConf(Properties confProp, String kpiCode) {
        //单独指标加工
        HashMap<String, String> kpiConfMap = new HashMap<>();
        Connection conn = MyJdbcUtils.getADBConnection(confProp);
        try {
            String sql = "select * from dim_comp_kpi where kpi_code =" + "'" + kpiCode + "'";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                kpiConfMap.put("KPI_CODE", rs.getString("KPI_CODE"));
                kpiConfMap.put("KPI_NAME", rs.getString("KPI_NAME"));
                kpiConfMap.put("COMP_KPI_DATA_TYPE", rs.getString("COMP_KPI_DATA_TYPE"));
                kpiConfMap.put("SRC_DATE_CYCLE_COL", rs.getString("SRC_DATE_CYCLE_COL"));
                kpiConfMap.put("SRC_PROV_COL", rs.getString("SRC_PROV_COL"));
                kpiConfMap.put("SRC_AREA_COL", rs.getString("SRC_AREA_COL"));
                kpiConfMap.put("DST_ENTITY_OWNER", rs.getString("DST_ENTITY_OWNER"));
                kpiConfMap.put("DST_ENTITY_NAME", rs.getString("DST_ENTITY_NAME"));
                kpiConfMap.put("SRC_ENTITY_OWNER", rs.getString("SRC_ENTITY_OWNER"));
                kpiConfMap.put("SRC_ENTITY_NAME", rs.getString("SRC_ENTITY_NAME"));
                kpiConfMap.put("COMP_KPI_TYPE", rs.getString("COMP_KPI_TYPE"));
                kpiConfMap.put("KPI_BATCH", rs.getString("KPI_BATCH"));
                kpiConfMap.put("IS_VALID", rs.getString("IS_VALID"));
                kpiConfMap.put("IS_SUM", rs.getString("IS_SUM"));
                kpiConfMap.put("SRC_VALUE_COL", rs.getString("SRC_VALUE_COL"));
                kpiConfMap.put("IS_AREA_SUM", rs.getString("IS_AREA_SUM"));
                kpiConfMap.put("UNIT_DESC", rs.getString("UNIT_DESC"));
                kpiConfMap.put("EDITOR", rs.getString("EDITOR"));
                kpiConfMap.put("CHANGE_TIME", rs.getString("CHANGE_TIME"));
                kpiConfMap.put("KPI_EXPLAIN", rs.getString("KPI_EXPLAIN"));
                kpiConfMap.put("COMP_KPI_CLASS", rs.getString("COMP_KPI_CLASS"));
                kpiConfMap.put("KPI_OWNER", rs.getString("KPI_OWNER"));
                kpiConfMap.put("COL_OPER1", rs.getString("COL_OPER1"));
                kpiConfMap.put("COL_OPER2", rs.getString("COL_OPER2"));
                kpiConfMap.put("COL_OPER3", rs.getString("COL_OPER3"));
                kpiConfMap.put("COL_OPER4", rs.getString("COL_OPER4"));
                kpiConfMap.put("COL_OPER5", rs.getString("COL_OPER5"));
                kpiConfMap.put("COL_OPER6", rs.getString("COL_OPER6"));
                kpiConfMap.put("COL_OPER7", rs.getString("COL_OPER7"));
                kpiConfMap.put("COL_OPER8", rs.getString("COL_OPER8"));
                kpiConfMap.put("COL_OPER9", rs.getString("COL_OPER9"));
                kpiConfMap.put("IS_SUPPORT_SUPER", rs.getString("IS_SUPPORT_SUPER"));
            }
            //关闭连接
            MyJdbcUtils.close(rs, pstmt, conn);
            return kpiConfMap;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (kpiConfMap.size() == 0) {
                throw new MyException(kpiCode + " 指标编码不存在！！");
            }
            return kpiConfMap;
        }
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 重写获取配置表信息的方法，可在短时间内使用同一个JDBC连接对象进行查询，减少计算指标反复创建连接的耗时，提高解析公式效率
     * @Date: 2022/11/26 15:33
     * @Param: [conn, kpiCode]
     * @return: java.util.HashMap<java.lang.String, java.lang.String>
     */
    public static HashMap<String, String> getKpiConf(Connection conn, String kpiCode) {
        //单独指标加工
        HashMap<String, String> kpiConfMap = new HashMap<>();
        try {
            String sql = "select * from dim_comp_kpi where kpi_code =" + "'" + kpiCode + "'";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                kpiConfMap.put("KPI_CODE", rs.getString("KPI_CODE"));
                kpiConfMap.put("KPI_NAME", rs.getString("KPI_NAME"));
                kpiConfMap.put("COMP_KPI_DATA_TYPE", rs.getString("COMP_KPI_DATA_TYPE"));
                kpiConfMap.put("SRC_DATE_CYCLE_COL", rs.getString("SRC_DATE_CYCLE_COL"));
                kpiConfMap.put("SRC_PROV_COL", rs.getString("SRC_PROV_COL"));
                kpiConfMap.put("SRC_AREA_COL", rs.getString("SRC_AREA_COL"));
                kpiConfMap.put("DST_ENTITY_OWNER", rs.getString("DST_ENTITY_OWNER"));
                kpiConfMap.put("DST_ENTITY_NAME", rs.getString("DST_ENTITY_NAME"));
                kpiConfMap.put("SRC_ENTITY_OWNER", rs.getString("SRC_ENTITY_OWNER"));
                kpiConfMap.put("SRC_ENTITY_NAME", rs.getString("SRC_ENTITY_NAME"));
                kpiConfMap.put("COMP_KPI_TYPE", rs.getString("COMP_KPI_TYPE"));
                kpiConfMap.put("KPI_BATCH", rs.getString("KPI_BATCH"));
                kpiConfMap.put("IS_VALID", rs.getString("IS_VALID"));
                kpiConfMap.put("IS_SUM", rs.getString("IS_SUM"));
                kpiConfMap.put("SRC_VALUE_COL", rs.getString("SRC_VALUE_COL"));
                kpiConfMap.put("IS_AREA_SUM", rs.getString("IS_AREA_SUM"));
                kpiConfMap.put("UNIT_DESC", rs.getString("UNIT_DESC"));
                kpiConfMap.put("EDITOR", rs.getString("EDITOR"));
                kpiConfMap.put("CHANGE_TIME", rs.getString("CHANGE_TIME"));
                kpiConfMap.put("KPI_EXPLAIN", rs.getString("KPI_EXPLAIN"));
                kpiConfMap.put("COMP_KPI_CLASS", rs.getString("COMP_KPI_CLASS"));
                kpiConfMap.put("KPI_OWNER", rs.getString("KPI_OWNER"));
                kpiConfMap.put("COL_OPER1", rs.getString("COL_OPER1"));
                kpiConfMap.put("COL_OPER2", rs.getString("COL_OPER2"));
                kpiConfMap.put("COL_OPER3", rs.getString("COL_OPER3"));
                kpiConfMap.put("COL_OPER4", rs.getString("COL_OPER4"));
                kpiConfMap.put("COL_OPER5", rs.getString("COL_OPER5"));
                kpiConfMap.put("COL_OPER6", rs.getString("COL_OPER6"));
                kpiConfMap.put("COL_OPER7", rs.getString("COL_OPER7"));
                kpiConfMap.put("COL_OPER8", rs.getString("COL_OPER8"));
                kpiConfMap.put("COL_OPER9", rs.getString("COL_OPER9"));
                kpiConfMap.put("IS_SUPPORT_SUPER", rs.getString("IS_SUPPORT_SUPER"));
            }
            return kpiConfMap;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (kpiConfMap.size() == 0) {
                throw new MyException(kpiCode + " 指标编码不存在！！");
            }
            return kpiConfMap;
        }
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 读取ETL配置信息，返回配置集合
     * @Date: 2022/11/20 17:45
     * @Param: [adbDriver, adbUrl, adbUser, adbPassword, kpiCode]
     * @return: java.util.HashMap<java.lang.String, java.lang.String>
     */
    public static HashMap<String, HashMap<Integer, String>> getEtlConf(Properties confProp, HashMap<String, String> kpiConfMap) {
        String srcTable = kpiConfMap.get("SRC_ENTITY_NAME");
        String dstTable = kpiConfMap.get("DST_ENTITY_NAME");
        //单独指标加工
        HashMap<String, HashMap<Integer, String>> etlConfMap = new HashMap<>();
        HashMap<Integer, String> dstColMap = new HashMap<>();
        HashMap<Integer, String> srcColMap = new HashMap<>();
        Connection conn = MyJdbcUtils.getADBConnection(confProp);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select dst_column from dim_entity_etl where src_entity_name =" +
                "'" + srcTable.toUpperCase().trim() + "'" +
                " and dst_entity_name =" +
                "'" + dstTable.toUpperCase().trim() + "'" +
                " order by CAST(ord1 AS INT) asc;";
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            int i = 0;
            while (rs.next()) {
                dstColMap.put(i, rs.getString("DST_COLUMN"));
                i++;
            }
        } catch (Exception e) {
            throw new MyException("\n系统异常：读取ETL配置表失败。\n少侠莫慌~\n可能是并发数量太高导致JDBC连接被拒，或ADB集群重启、故障等。");
        }

        //查询来源表字段信息
        sql = "select src_column from dim_entity_etl where src_entity_name =" +
                "'" + srcTable.toUpperCase().trim() + "'" +
                " and dst_entity_name =" +
                "'" + dstTable.toUpperCase().trim() + "'" +
                " order by CAST(ord1 AS INT) asc;";
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            int i = 0;
            while (rs.next()) {
                if (StringUtils.isNullOrEmpty(rs.getString("SRC_COLUMN"))||rs.getString("SRC_COLUMN").trim().isEmpty()||!(rs.getString("SRC_COLUMN") instanceof String)) {
                    srcColMap.put(i, "NULL");
                } else {
                    srcColMap.put(i, rs.getString("SRC_COLUMN"));
                }
                i++;
            }
        } catch (Exception e) {
            throw new MyException("\n系统异常：读取ETL配置表失败。\n少侠莫慌~\n可能是并发数量太高导致JDBC连接被拒，或ADB集群重启、故障等。");
        }
        etlConfMap.put("srcColMap", srcColMap);
        etlConfMap.put("dstColMap", dstColMap);
        if (srcColMap.isEmpty()) {
            throw new MyException("\n指标配置问题：\n目标表 " + dstTable + "中的维度字段 " + CommonCheck.dstColList + " 并未在来源表全部自动匹配，\n请在ETL表： dim_entity_etl 中配置来源表： " + srcTable + " 到目标表 " + dstTable + " 之间的字段映射关系！！");
        }
        //关闭连接
        MyJdbcUtils.close(rs, pstmt, conn);
        return etlConfMap;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 获取计算指标的ETL配置（由于计算指标的来源表和目标表相同，都是结果表，所以可以通过窗口函数获取
     * @Date: 2022/11/25 13:27
     * @Param: []
     * @return: java.util.HashMap<java.lang.Integer, java.lang.String>
     */
    public static HashMap<Integer, String> getJSEtlConf(Properties confProp, HashMap<String, String> kpiConfMap) {
        HashMap<Integer, String> dstColMap = new HashMap<>();
        Connection conn = MyJdbcUtils.getADBConnection(confProp);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "SELECT DST_COLUMN,\n" +
                "\tORD1\n" +
                "FROM DIM_ENTITY_ETL\n" +
                "WHERE DST_ENTITY_NAME = '" + kpiConfMap.get("DST_ENTITY_NAME") + "'\n" +
                "GROUP BY DST_COLUMN,\n" +
                "\tORD1\n" +
                "ORDER BY CAST(ORD1 AS INT)\n";
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            int i = 0;
            while (rs.next()) {
                dstColMap.put(i, rs.getString("DST_COLUMN"));
                i++;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (dstColMap.size() == 0) {
                throw new MyException("\nEtl配置问题：\n该计算指标所依赖的基础指标还未配置Etl映射关系！！");
            }
        }
        MyJdbcUtils.close(rs, pstmt, conn);
        return dstColMap;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 重写获取计算指标的方法，可通过同一个 JDBC连接对象进行配置表查询，减少建立连接的耗时，提高解析效率
     * @Date: 2022/11/26 15:37
     * @Param: [conn, kpiConfMap]
     * @return: java.util.HashMap<java.lang.Integer, java.lang.String>
     */
    public static HashMap<Integer, String> getJSEtlConf(Connection conn, HashMap<String, String> kpiConfMap) {
        HashMap<Integer, String> dstColMap = new HashMap<>();
        String sql = "SELECT DST_COLUMN,\n" +
                "\tORD1\n" +
                "FROM DIM_ENTITY_ETL\n" +
                "WHERE DST_ENTITY_NAME = '" + kpiConfMap.get("DST_ENTITY_NAME") + "'\n" +
                "GROUP BY DST_COLUMN,\n" +
                "\tORD1\n" +
                "ORDER BY CAST(ORD1 AS INT)\n";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            int i = 0;
            while (rs.next()) {
                dstColMap.put(i, rs.getString("DST_COLUMN"));
                i++;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (dstColMap.size() == 0) {
                throw new MyException("\nEtl配置问题：\n 计算指标" + "所依赖的基础指标" + kpiConfMap.get("KPI_CODE") + "还未配置Etl映射关系！！\n" + "\n执行的查询语句为：\n" + sql);
            }
        }
        return dstColMap;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 获取基础指标联动的计算指标列表
     * @Date: 2022/11/27 21:27
     * @Param: [kpiConfMap, confProp]
     * @return: java.util.List<java.lang.String>
     */
    public static List<String> getJsList(HashMap<String, String> kpiConfMap, Properties confProp) {
        Connection conn = MyJdbcUtils.getADBConnection(confProp);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<String> jsKpiCodeList = new ArrayList<>();
        String sql = "SELECT KPI_CODE\n" +
                "FROM DIM_COMP_KPI\n" +
                "WHERE KPI_OWNER = 'GX' AND COMP_KPI_TYPE = 'JS'\n" +
                "\tAND IS_VALID = '1'\n" +
                "\tAND SRC_VALUE_COL LIKE '%{" + kpiConfMap.get("KPI_CODE") + "%'";
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                jsKpiCodeList.add(rs.getString("KPI_CODE"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return jsKpiCodeList;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 列表去重
     * @Date: 2022/11/21 15:18
     * @Param: [list]
     * @return: java.util.List
     */
    public static List<String> removeDuplicationByContains(List<String> list) {
        List<String> tempList = new ArrayList<>(list);
        List<String> newList = new ArrayList<>();
        for (int i = 0; i < tempList.size(); i++) {
            boolean isContains = newList.contains(tempList.get(i));
            if (!isContains) {
                newList.add(tempList.get(i));
            }
        }
        tempList.clear();
        tempList.addAll(newList);
        return tempList;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 以逗号分隔，获取指定位置的元素值
     * 若传入的公式没有分隔符，则返回NOR操作类型的公式参数，将年、月、日的移动置为0
     * @Date: 2022/11/23 11:39
     * @Param: [formula, index]
     * @return: java.lang.String
     */
    public static String getSplitByIndex(String formular, int index) {
        String res = null;
        if (formular.contains(",")) {
            String[] split = formular.split(",");
            try {
                res = split[index];
            } catch (Exception e) {
                res = "0";
            }
        } else {
            if (index == 0) {
                res = formular;
            } else if (index == 1) {
                res = "NOR";
            } else {
                res = "0";
            }
        }
        return res;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 解析计算公式的 {} 内容，返回所有计算公式中的单元公式内容
     * @Date: 2022/11/21 15:25
     * @Param: [formular]
     * @return: java.util.List<java.lang.String>
     */
    public static List<String> getFormularArray(String formular) {
        List<String> res = new ArrayList<>();
        int n = 0;
        String[] arr1 = new String[80];
        for (int i = 0; i < arr1.length; i++) {
            if (formular.contains("{")) {
                res.add(formular.substring(formular.indexOf("{") + 1, formular.indexOf("}")));
                formular = formular.substring(formular.indexOf("}") + 1);
            } else {
                break;
            }
        }
        return res;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 解析计算公式的 {} 内容，返回所有计算公式中的单元公式内容
     * @Date 2023/4/10 22:09
     * @Param: [formular]
     * @Return: java.util.List<java.lang.String>
     */
    public static List<String> getFormularList(String formular) {
        List<String> formularList = new ArrayList<>();
        // 定义正则表达式
        String regex = "\\{[^{}]*\\}";
        // 创建Pattern对象
        Pattern pattern = Pattern.compile(regex);
        // 创建Matcher对象
        Matcher matcher = pattern.matcher(formular);
        // 循环查找所有匹配项
        while (matcher.find()) {
            formularList.add(matcher.group().replaceAll("[{}]", ""));
        }
        return formularList;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据指定的分割字符串列表，将指定内容切分
     * @Date 2023/4/10 22:01
     * @Param: [content, splitArray]
     * @Return: java.lang.String[]
     */
//    public static String[] splitByArray(String content, List<String> splitArray) {
//        // 将splitArray中的字符串拼成正则表达式
//        String splitRegex = String.join("|", splitArray.stream().map(Pattern::quote).collect(Collectors.toList()));
//        System.out.println(splitRegex);
//        // 使用split()方法拆分字符串
//        return content.split(splitRegex);
//    }


    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 按照给定的字符串列表顺序切分字符串内容
     * @Date 2023/4/18 10:15
     * @Param: [content, splitArray]
     * @Return: java.util.List<java.lang.String>
     */
    public static List<String> splitByArray(String content, List<String> splitArray) {
        List<String> temp = new ArrayList<>();
        for (int i=0;i<splitArray.size();i++){
            String[] splitRes = content.split(splitArray.get(i), 2);
            if (i==splitArray.size()-1){
                Collections.addAll(temp,splitRes[0],splitRes[1]);
            }else {
                Collections.addAll(temp,splitRes[0]);
                content = splitRes[1];
            }
        }
        return temp;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 将计算公式按照拆分的公式，对公式单元以外的0进行精度替换
     * @Date 2023/4/10 22:19
     * @Param: [formular]
     * @Return: java.lang.String
     */
    public static String replaceFormular(String formular) {
        StringBuilder replacedFormular = new StringBuilder();
        List<String> formularList = CommonFun.getFormularList(formular);
//        String[] strings = CommonFun.splitString(formular, formularList);
        List<String> strings = CommonFun.splitByArray(formular, formularList);
        List<String> replaced = new ArrayList<>();
        for (int i = 0; i < strings.size(); i++) {
            replaced.add(strings.get(i).replace("0", "CAST(0 AS DECIMAL(38,18))"));
        }
        for (int i = 0; i < replaced.size(); i++) {
            if (i < replaced.size() - 1) {
                replacedFormular.append(replaced.get(i)).append(formularList.get(i));
            } else {
                replacedFormular.append(replaced.get(i));
            }
        }
        return replacedFormular.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 传入账期和计算公式中配置的 年、月、日 的值，返回对应的计算公式表达的起点值
     * 值：是时间范围Y M D 变量的实例，Y表示年，M 表示月 D 表示日。
     * 它们的值特点是小于等于0的整数，某些计算类型含有特殊字母表示的时间范围，如ST表示初、EN表示末。
     * @Date: 2022/11/22 14:32
     * @Param: [acctDate, year, month, day] 其中 year\month\day 表示加减的数值
     * @return: java.lang.String
     */
    public static String genSpeBeginDate(String acctDate, String formular) {
        //时间范围：年
        String year = CommonFun.getSplitByIndex(formular, 2);
        //时间范围：月
        String month = CommonFun.getSplitByIndex(formular, 3);
        //时间范围：日
        String day = CommonFun.getSplitByIndex(formular, 4);
        //初始化返回值
        String res = acctDate;
        //初始化Calender对象
        Calendar cal = Calendar.getInstance();
        //日指标账期格式化对象
        SimpleDateFormat dayDf = new SimpleDateFormat("yyyyMMdd");
        //月指标账期格式化对象
        SimpleDateFormat monDf = new SimpleDateFormat("yyyyMM");
        if (acctDate.length() == 6) {
            //当账期为月指标时，初始化当前账期的calender对象值
            cal.set(Calendar.YEAR, Integer.valueOf(acctDate.substring(0, 4)));
            cal.set(Calendar.MONTH, Integer.valueOf(acctDate.substring(4, 6))-1);
            //根据输入的year值计算账期
            try {
                cal.add(Calendar.YEAR, Integer.valueOf(year));
            } catch (NumberFormatException e) {
                throw new MyException("计算公式配置错误：计算公式中(年的移动位)只允许使用数字。");
            }
            //根据输入的month值计算账期
            if ("ST".equals(month)) {
                cal.set(Calendar.MONTH, JANUARY);
            } else if ("EN".equals(month)) {
                cal.set(Calendar.MONTH, DECEMBER);
            } else if (isNumber(month)) {
                cal.add(Calendar.MONTH, Integer.valueOf(month));
            } else {
                throw new MyException("计算公式配置错误：" + month + " 在计算公式中不支持。");
            }
            res = monDf.format(cal.getTime());
        } else if (acctDate.length() == 8) {
            //当账期为日指标时
            cal.set(Calendar.YEAR, Integer.valueOf(acctDate.substring(0, 4)));
            cal.set(Calendar.MONTH, Integer.valueOf(acctDate.substring(4, 6))-1);
            cal.set(Calendar.DATE, Integer.valueOf(acctDate.substring(6, 8)));
            try {
                cal.add(Calendar.YEAR, Integer.valueOf(year));
            } catch (NumberFormatException e) {
                throw new MyException("计算公式配置错误：计算公式中(年的移动位)只允许使用数字。");
            }
            //根据输入的month值计算账期
            if ("ST".equals(month)) {
                cal.set(Calendar.MONTH, JANUARY);
            } else if ("EN".equals(month)) {
                cal.set(Calendar.MONTH, DECEMBER);
            } else if (isNumber(month)) {
                cal.add(Calendar.MONTH, Integer.valueOf(month));
            } else {
                throw new MyException("计算公式配置错误：" + month + " 在计算公式中不支持。");
            }
            if ("ST".equals(day)) {
                cal.set(Calendar.DATE, 1);
            } else if ("EN".equals(day)) {
                Calendar calMid = Calendar.getInstance();
                calMid = cal;
                calMid.add(Calendar.MONTH, 1);
                calMid.set(Calendar.DATE, 1);
                calMid.add(Calendar.DATE, -1);
                cal.set(Calendar.DATE, calMid.get(Calendar.DATE));
            } else if (isNumber(day)) {
                cal.add(Calendar.DATE, Integer.valueOf(day));
            } else if (day.isEmpty()) {
                throw new MyException("计算公式配置错误：日指标计算公式缺少参数。");
            } else {
                throw new MyException("计算公式配置错误：" + day + "在计算公式中不支持。");
            }
            res = dayDf.format(cal.getTime());

        } else {
            throw new MyException("调度错误：账期参数格式错误！！");
        }
        return res;
    }


    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 传入账期和计算公式中配置的 年、月、日 的值，返回对应的计算公式表达的终点值
     * 值：是时间范围Y M D 变量的实例，Y表示年，M 表示月 D 表示日。
     * 它们的值特点是小于等于0的整数，某些计算类型含有特殊字母表示的时间范围，如ST表示初、EN表示末。T表示初、EN表示末。
     * @Date 2023/3/11 11:50
     * @Param: [acctDate, formular]
     * @Return: java.lang.String
     */
    public static String genSpeEndDate(String acctDate, String formular) {
        //计算类型
        String jsType = CommonFun.getSplitByIndex(formular, 1);
        //时间范围：年
        String year = CommonFun.getSplitByIndex(formular, 2);
        //时间范围：月
        String month = CommonFun.getSplitByIndex(formular, 3);
        //时间范围：日
        String day = CommonFun.getSplitByIndex(formular, 4);
        //初始化返回值
        String res = acctDate;
        //初始化Calender对象
        Calendar cal = Calendar.getInstance();
        //日指标账期格式化对象
        SimpleDateFormat dayDf = new SimpleDateFormat("yyyyMMdd");
        //月指标账期格式化对象
        SimpleDateFormat monDf = new SimpleDateFormat("yyyyMM");
        if (acctDate.length() == 6) {
            //当账期为月指标时，初始化当前账期的calender对象值
            cal.set(Calendar.YEAR, Integer.valueOf(acctDate.substring(0, 4)));
            cal.set(Calendar.MONTH, Integer.valueOf(acctDate.substring(4, 6))-1);
            //根据输入的year值计算账期
            try {
                cal.add(Calendar.YEAR, Integer.valueOf(year));
            } catch (NumberFormatException e) {
                throw new MyException("计算公式配置错误：计算公式中(年的移动位)只允许使用数字。");
            }
            if (jsType.equals("SUM") && Integer.valueOf(year) == 0) {
                cal.add(Calendar.MONTH, -1);
            }
            res = monDf.format(cal.getTime());
        } else if (acctDate.length() == 8) {
            //当账期为日指标时
            cal.set(Calendar.YEAR, Integer.valueOf(acctDate.substring(0, 4)));
            cal.set(Calendar.MONTH, Integer.valueOf(acctDate.substring(4, 6))-1);
            cal.set(Calendar.DATE, Integer.valueOf(acctDate.substring(6, 8)));
            try {
                cal.add(Calendar.YEAR, Integer.valueOf(year));
            } catch (NumberFormatException e) {
                throw new MyException("计算公式配置错误：计算公式中(年的移动位)只允许使用数字。");
            }
            //根据输入的month值计算账期
            if ("ST".equals(month)) {
                cal.set(Calendar.MONTH, JANUARY);
            } else if ("EN".equals(month)) {
                cal.set(Calendar.MONTH, DECEMBER);
            } else if (isNumber(month)) {
                cal.add(Calendar.MONTH, Integer.valueOf(month));
                //若计算类型是SUM，且年月移动值都是0，则计算累计值为当期累计，只能支撑账期初到T-1的范围计算，外层再和当期值相加
                if (jsType.equals("SUM") && Integer.valueOf(year) == 0 && Integer.valueOf(month) == 0) {
                    cal.add(Calendar.DATE, -1);
                }
            } else {
                throw new MyException("计算公式配置错误：" + month + " 在计算公式中不支持。");
            }
            res = dayDf.format(cal.getTime());
        } else {
            throw new MyException("调度错误：账期参数格式错误！！");
        }
        return res;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据账期进行判断，返回公式代表的日期和当前账期的时间间隔，做除法时，时间间隔不能为0
     * @Date: 2022/11/24 16:51
     * @Param: [acctDate, formular]
     * @return: java.lang.String
     */
    public static long getTimeDistance(String acctDate, String formular) {
        String beginDate = genSpeBeginDate(acctDate, formular);
        //时间范围：年
        String jsType = CommonFun.getSplitByIndex(formular, 1);
        //时间范围：年
        String year = CommonFun.getSplitByIndex(formular, 2);
        //时间范围：月
        String month = CommonFun.getSplitByIndex(formular, 3);
        //时间范围：日
        String day = CommonFun.getSplitByIndex(formular, 4);
        //初始化返回值
        long res = 0;
        //初始化Calender对象
        Calendar cal = Calendar.getInstance();
        //日指标账期格式化对象
        SimpleDateFormat dayDf = new SimpleDateFormat("yyyyMMdd");
        //月指标账期格式化对象
        SimpleDateFormat monDf = new SimpleDateFormat("yyyyMM");
        if (acctDate.length() == 8) {
            //如果账期是日账期，则返回相差的天数
            try {
                Date startTime = dayDf.parse(beginDate);
                Date endTime = dayDf.parse(genSpeEndDate(acctDate, formular));
                res = dayDiff(startTime, endTime);
            } catch (ParseException e) {
                e.printStackTrace();
            }

        } else if (acctDate.length() == 6) {
            //如果账期是月账期，则返回相差的月份数
            try {
                Date startTime = monDf.parse(beginDate);
                Date endTime = monDf.parse(genSpeEndDate(acctDate, formular));
                res = getMonthDiff(startTime, endTime);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        return res;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 计算两个日期的相差天数
     * @Date: 2022/11/24 17:58
     * @Param: [startTime, endTime]
     * @return: int
     */
    public static long dayDiff(Date startTime, Date endTime) {
        long daysDiff = 0;
        long i = endTime.getTime();
        long j = startTime.getTime();
        daysDiff = (long) (endTime.getTime() - startTime.getTime()) / (1000 * 60 * 60 * 24);
        return daysDiff;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 获取两个日期相差的月份数
     * @Date: 2022/11/24 17:51
     * @Param: [d1, d2]
     * @return: int
     */
    public static long getMonthDiff(Date startTime, Date endTime) {
        Calendar c1 = Calendar.getInstance();
        Calendar c2 = Calendar.getInstance();
        c1.setTime(startTime);
        c2.setTime(endTime);
        int year1 = c1.get(Calendar.YEAR);
        int year2 = c2.get(Calendar.YEAR);
        int month1 = c1.get(Calendar.MONTH);
        int month2 = c2.get(Calendar.MONTH);
        int day1 = c1.get(Calendar.DAY_OF_MONTH);
        int day2 = c2.get(Calendar.DAY_OF_MONTH);
        // 获取年的差值
        int yearInterval = year1 - year2;
        // 如果 d1的 月-日 小于 d2的 月-日 那么 yearInterval-- 这样就得到了相差的年数
        if (month1 < month2 || month1 == month2 && day1 < day2) {
            yearInterval--;
        }
        // 获取月数差值
        int monthInterval = (month1 + 12) - month2;
        if (day1 < day2) {
            monthInterval--;
        }
        monthInterval %= 12;
        long monthsDiff = Math.abs(yearInterval * 12 + monthInterval);
        return monthsDiff;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 判断传入额字符串是否是数值
     * @Date: 2022/11/22 15:50
     * @Param: [str]
     * @return: boolean
     */
    public static boolean isNumber(String str) {
        try {
            Double.parseDouble(str);
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据输入的参数确定限定条件关键词
     * @Date: 2022/11/22 18:24
     * @Param: [acctDate]
     * @return: java.lang.String
     */
    public static String dateType(String acctDate) {
        if (acctDate.length() == 6) {
            return "MONTH_ID";
        } else if (acctDate.length() == 8) {
            return "DATE_ID";
        } else {
            throw new MyException("调度错误：账期参数格式错误！！");
        }
    }


    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description:
     * @Date: 2022/11/22 21:10
     * @Param: [kpiCode, confMap]
     * @return: java.lang.Boolean
     */
    public static Boolean isJSCode(HashMap<String, String> kpiConfMap) {
        if (kpiConfMap.get("COMP_KPI_TYPE").equals("JS")) {
            return Boolean.TRUE;
        } else {
            return Boolean.FALSE;
        }
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 判断字符串内容是否为全大写
     * @Date: 2022/11/27 13:00
     * @Param: [str]
     * @return: java.lang.Boolean
     */
    public static Boolean isUpperCase(String str) {
        for (int i = 0; i < str.length(); i++) {
            if (Character.isLetter(str.charAt(i))) {
                if (!isChineseChar(str.charAt(i))) {
                    if (!Character.isUpperCase(str.charAt(i))) {
                        return Boolean.FALSE;
                    }
                }
            }
        }
        return Boolean.TRUE;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 判断一个字符是否是汉字
     * @Date: 2022/12/2 19:21
     * @Param: [c]
     * @return: java.lang.Boolean
     */
    public static Boolean isChineseChar(char c) {
        return String.valueOf(c).matches("[\u4e00-\u9fa5]");
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 查询元数据表，获取表的字段，按创建顺序排序
     * @Date: 2022/11/30 10:03
     * @Param: [kpiConfMap, confProp]
     * @return: java.util.List<java.lang.String>
     */
    public static List<String> getColumDesc(String tableSchema, String tableName, Connection conn) {
        List<String> list = new ArrayList<>();
        String sql = "SELECT column_name\n" +
                "FROM information_schema.columns\n" +
                "WHERE TABLE_SCHEMA = '" + tableSchema + "'\n" +
                "\tAND TABLE_NAME = '" + tableName + "'\n" +
                "\tAND extra != 'auto_increment'" +
                "ORDER BY ordinal_position";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(rs.getString("COLUMN_NAME").toUpperCase());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        //对建表时大小写进行检测
        list.stream().forEach(col -> {
            if (!isUpperCase(col)) {
                throw new MyException("\n建表规范：\n" + tableName + " 应当在建表时使用全大写。");
            }
        });
        return list;
    }


    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 查询Mc元数据表，获取表的字段，按创建顺序排序
     * @Date 2023/4/5 18:43
     * @Param: [tableSchema, tableName, conn]
     * @Return: java.util.List<java.lang.String>
     */
    public static List<String> getMcColumDesc(String tableSchema, String tableName, Properties confProp) {
        String projectName = confProp.getProperty("project_name");
        String accessId = confProp.getProperty("access_id");
        String accessKey = confProp.getProperty("access_key");
        String endPoint = confProp.getProperty("end_point");
        StringBuilder command = new StringBuilder();
        StringBuilder safeCommand = new StringBuilder();

        command.append("odps --project=").append(projectName)
                .append(" --endpoint=").append(endPoint)
                .append(" -u ").append(accessId)
                .append(" -p ").append(accessKey)
                .append(" -e 'DESC ").append(tableSchema.replace("CUBE_GX", "ITSY_CUBE")).append(".").append(tableName).append("'")
                .append(" | grep -v Partition | grep -v +--")
                .append(" | awk '/Field/{flag=1} flag' | sed -e '1d' -e '$d' | awk -F \"|\" '{print $2}'");
        safeCommand.append("odps --project=").append(projectName)
                .append(" --endpoint=").append(endPoint)
                .append(" -u ").append(accessId)
                .append(" -p ").append("******")
                .append(" -e 'DESC ").append(tableSchema.replace("CUBE_GX", "ITSY_CUBE")).append(".").append(tableName).append("'")
                .append(" | grep -v Partition | grep -v +--")
                .append(" | awk '/Field/{flag=1} flag' | sed -e '1d' -e '$d' | awk -F \"|\" '{print $2}'");
        CommonTool.outputInfoTip("查询MC表结构元数据信息：" + safeCommand.toString());
        List<String> fieldList = ShellUtils.callShellByExecAndReturnUpper(command.toString());
        return fieldList;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 查询元数据表，获取表的字段，按创建顺序排序
     * @Date: 2022/11/30 10:03
     * @Param: [kpiConfMap, confProp]
     * @return: java.util.List<java.lang.String>
     */
    public static List<String> getColumDesc(String tableName, Properties confProp) {
        Connection conn = MyJdbcUtils.getADBConnection(confProp);
        List<String> list = new ArrayList<>();
        String sql = "SELECT column_name\n" +
                "FROM information_schema.columns\n" +
                "WHERE TABLE_SCHEMA = 'CUBE_GX'\n" +
                "\tAND TABLE_NAME = '" + tableName + "'\n" +
                "ORDER BY ordinal_position";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(rs.getString("column_name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyJdbcUtils.close(rs, pstmt, conn);
        }
        return list;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 从列表A中删除列表B中的有的内容
     * @Date: 2022/12/2 16:06
     * @Param: [listA, listB]
     * @return: java.util.List<java.lang.String>
     */
    public static List<String> listrem(List<String> listA, List<String> listB) {
        HashSet hs1 = new HashSet(listA);
        HashSet hs2 = new HashSet(listB);
        hs1.removeAll(hs2);
        List<String> listC = new ArrayList<String>();
        listC.addAll(hs1);
        return listC;
    }


    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 将一组数据固定分组，每组n个元素
     * @Date: 2023/2/3 10:12
     * @Param: [source, n]
     * @return: java.util.List<java.util.List < T>>
     */
    public static List<List<String>> fixedGrouping(List<String> source, int n) {
        if (null == source || source.size() == 0 || n <= 0) {
            return null;
        }
        List<List<String>> result = new ArrayList<List<String>>();
        int sourceSize = source.size();
        int size = (source.size() / n) + 1;
        for (int i = 0; i < size; i++) {
            List<String> subset = new ArrayList<String>();
            for (int j = i * n; j < (i + 1) * n; j++) {
                if (j < sourceSize) {
                    subset.add(source.get(j));
                }
            }
            result.add(subset);
        }
        return result;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 将List集合转为逗号分隔的字符串
     * @Date: 2023/2/3 11:56
     * @Param: []
     * @return: java.lang.String
     */
    public static String listToString(List<String> jsList) {
        String jsListMid = jsList.toString();
        jsListMid = jsListMid.substring(1, jsListMid.length());
        jsListMid = jsListMid.substring(0, jsListMid.length() - 1).replaceAll(" ", "");
        return jsListMid;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 将MC的Decode函数进行数值类型转换
     * @Date 2023/4/9 10:11
     * @Param: [formular]
     * @Return: java.lang.String
     */
    public static String convertMcDecode(String formular) {
        if (formular.toUpperCase().contains("DECODE")) {
            formular = replaceFormular(formular);
        }
        return formular;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据给定账期获取上月最后一天的账期
     * @Date 2023/4/11 12:08
     * @Param: [acctDate]
     * @Return: java.lang.String
     */
    public static String getLastDayOfPreviousMonth(String acctDate) {
        // 获取上个月的Calendar实例
        Calendar calendar = Calendar.getInstance();
        calendar.set(Calendar.YEAR,Integer.valueOf(acctDate.substring(0,4)));
        calendar.set(Calendar.MONTH,Integer.valueOf(acctDate.substring(4,6)));
        calendar.add(Calendar.MONTH, -1);
        // 设置日期为该月最后一天
        calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
        // 格式化日期字符串
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        return sdf.format(calendar.getTime());
    }


    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据当前账期和指定的自定义变量标识，生成对应的账期
     * @Date 2023/4/17 16:47
     * @Param: [acctDate, defDate]
     * @Return: java.lang.String
     */
    public static String getdefDate(String acctDate,String defDate) {
        SimpleDateFormat sdf = null;
        String[] s = defDate.split("_");
        String num = s[1].replaceAll("[^\\d]", "");
        String type = s[2];
        if (acctDate.length()==6){
            // 获取上个月的Calendar实例
            Calendar calendar = Calendar.getInstance();
            calendar.set(Calendar.YEAR,Integer.valueOf(acctDate.substring(0,4)));
            calendar.set(Calendar.MONTH,Integer.valueOf(acctDate.substring(4,6)));
            calendar.add(Calendar.MONTH, -Integer.valueOf(num)-1);
            // 格式化日期字符串
            if ("MONTH".equals(type)){
                sdf = new SimpleDateFormat("yyyyMM");
            }else if ("YEAR".equals(type)){
                sdf = new SimpleDateFormat("yyyy");

            }
            return "'"+sdf.format(calendar.getTime())+"'";
        }
        return acctDate;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 匹配内容中符合特定账期标识的内容
     * @Date 2023/4/17 17:21
     * @Param: [content]
     * @Return: java.util.List<java.lang.String>
     */
    public static List<String> getDefDate(String content){
        List<String> defDateList = new ArrayList<>();
        String pattern = "V_L([0-9]+)_(MONTH|YEAR)";
        Pattern p = Pattern.compile(pattern);
        Matcher m = p.matcher(content);
        while (m.find()) {
            defDateList.add(m.group());
        }
        return defDateList;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据指标加工的数据账期和配置内容中的特定账期标识进行批量替换成对应限制账期
     * @Date 2023/4/17 17:37
     * @Param: [acctDate, content]
     * @Return: java.lang.String
     */
    public static String replaceDefDate(String acctDate,String content){
        List<String> defDateList = CommonFun.getDefDate(content);
        for (int i=0;i<defDateList.size();i++){
            content = content.replace(defDateList.get(i),CommonFun.getdefDate(acctDate, defDateList.get(i)));
        }

        return content;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 去除HashMap中的重复值并生成新的HashMap
     * @Date 2023/4/21 17:27
     * @Param: [srcColMap]
     * @Return: java.util.HashMap<java.lang.Integer,java.lang.String>
     */
    public static HashMap<Integer,String> removeDuplicateValuesFromHashMap(HashMap<Integer,String> srcColMap){
        List<String> colList = new ArrayList<>();
        srcColMap.keySet().stream().distinct().forEach(k-> colList.add(srcColMap.get(k)));
        List<String> newColList = colList.stream().distinct().collect(Collectors.toList());
        srcColMap.clear();
        for (int i=0;i<newColList.size();i++){
            srcColMap.put(i+1,newColList.get(i));
        }
        return srcColMap;
    }
}
